SET work_mem = '512 MB';

DROP TABLE IF EXISTS price_generated_test;
DROP TABLE IF EXISTS _prices_to_insert;

CREATE TEMPORARY TABLE price_generated_test
(
  price_generated_id uuid NOT NULL DEFAULT gen_random_uuid(),
  product_id uuid NOT NULL,
  company_id uuid NOT NULL,
  date_range daterange NOT NULL,
  average_price numeric NOT NULL,
  average_price_delivered numeric NOT NULL,
  low_price numeric NOT NULL,
  low_price_delivered numeric NOT NULL,
  high_price numeric NOT NULL,
  high_price_delivered numeric NOT NULL,
  uom_type_id uuid NOT NULL,
  active_range tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone),
  CONSTRAINT price_generated_test_pkey PRIMARY KEY (price_generated_id),
  CONSTRAINT price_generated_test_company_product_date_active_excl EXCLUDE 
  USING gist (gosimple.hex_to_bigint(company_id::text) WITH =, gosimple.hex_to_bigint(product_id::text) WITH =, date_range WITH &&, active_range WITH &&)
);

CREATE INDEX idx_price_generated_test_prod_comp_date
  ON price_generated_test
  USING btree
  (product_id, company_id, date_range);


INSERT INTO price_generated_test (product_id, company_id, date_range, average_price, average_price_delivered, low_price, low_price_delivered, high_price, high_price_delivered, uom_type_id, active_range)
SELECT p.product_id, c.company_id, d.date_range, avg(pr.price), avg(pr.price + pr.delivery), min(pr.price), min(pr.price + pr.delivery), max(pr.price), max(pr.price + pr.delivery), u.uom_type_id, ar.active_range
FROM (
	SELECT gen_random_uuid() as product_id
	FROM generate_series(1, 200)
) p
CROSS JOIN (
	SELECT gen_random_uuid() as company_id
	FROM generate_series(1, 60)
) c
CROSS JOIN (
	SELECT daterange((now() + (generate_series || ' months')::interval)::date, (now() + (generate_series + 1 || ' months')::interval)::date) as date_range
	FROM generate_series(1, 24)
) d
CROSS JOIN (
	SELECT gen_random_uuid() as uom_type_id
	FROM generate_series(1, 1)
) u 
CROSS JOIN (
	SELECT random() * 15 as price, random() * 3 as delivery
	FROM generate_series(1, 15)
) pr
CROSS JOIN (
	SELECT tstzrange(now() - (generate_series || ' days')::interval, CASE WHEN now() - (generate_series || ' days')::interval = now() THEN null ELSE now() - (generate_series || ' days')::interval END, '[]') as active_range
	FROM generate_series(0, 10, 10)
) ar
GROUP BY p.product_id, c.company_id, d.date_range, u.uom_type_id, ar.active_range;


CREATE TEMPORARY TABLE _prices_to_insert AS
SELECT product_id, company_id, date_range, average_price * random() * 1.5 average_price, average_price_delivered * random() * 1.5 average_price_delivered, low_price, low_price_delivered, high_price, high_price_delivered, uom_type_id
FROM price_generated_test
WHERE active_range @> now()
ORDER BY random()
LIMIT 200;



BEGIN;
-- If this index exists this test case will use it for the update
DROP INDEX idx_price_generated_test_prod_comp_date;
-- If this is enabled it will choose a seq scan where my regular table still won't
SET enable_seqscan = false;


UPDATE price_generated_test
SET active_range = tstzrange(lower(active_range), now())
WHERE upper(price_generated_test.active_range) IS NULL
AND EXISTS (
	SELECT 1
	FROM _prices_to_insert pti
	WHERE price_generated_test.product_id = pti.product_id
	AND price_generated_test.company_id = pti.company_id
	AND price_generated_test.date_range = pti.date_range);